/*
 * WARNING: This script is intended for installation of the Daycare software. Running this script on a SQL Server
 * instance where Daycare has already been installed will reset your Daycare database to its default level and delete all content. 
*/

----------------------------------------------------------------------------
/* Creation of the Database */ 
USE master

/* Drop the table if it already exists. */
IF EXISTS ( SELECT  *
            FROM    sys.sysdatabases
            WHERE   name = 'Daycare' ) 
  BEGIN
		/* This ALTER DATABASE command removes connections so the DB can be dropped */
    ALTER DATABASE Daycare SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Daycare
  END

 /* Create the DB file and logs file. */
CREATE DATABASE Daycare ON
( 
	NAME = Daycare_dat,
	FILENAME = 'C:\program files\Daycare\Data\Daycare_data.mdf',
	SIZE = 50MB,
	FILEGROWTH=10MB
) LOG ON
(
	NAME = Daycare_log,
	FILENAME = 'C:\program files\Daycare\Data\Daycare_log.ldf',
	SIZE = 5MB,
	FILEGROWTH=1MB
)

ALTER DATABASE Daycare
SET RECOVERY SIMPLE
GO

-------------------------------------------------------------------------
/* Creation of the DaycareAdmin user. */

/* If the login doesn't already exist, create it. If it does exist, reset the password. */
IF NOT EXISTS ( SELECT  principal_id
                FROM    sys.server_principals
                WHERE   name = 'DaycareAdmin' ) 
  CREATE LOGIN DaycareAdmin WITH PASSWORD = 'Welcome1';
ELSE 
  ALTER LOGIN DaycareAdmin WITH PASSWORD = 'Welcome1';


-------------------------------------------------------------------------
/* Create the DaycareAdmin user in the DaycareAdmin db. */
USE Daycare
GO
CREATE USER DaycareAdmin FOR LOGIN DaycareAdmin
EXEC sp_addrolemember 'db_datareader','DaycareAdmin'
EXEC sp_addrolemember 'db_datawriter','DaycareAdmin'
GO

-------------------------------------------------------------------------
/* Create schema and add permissions */

IF NOT EXISTS ( SELECT  *
                FROM    sys.schemas
                WHERE   name = 'Daycare' ) 
  BEGIN
    EXEC ('CREATE SCHEMA Daycare')
  END

GRANT EXECUTE ON SCHEMA::dbo TO DaycareAdmin
GO
GRANT EXECUTE ON SCHEMA::daycare TO DaycareAdmin
GO